/**IMPORT OTHER KITCHEN CATEGORIES DATA FROM AMAZON***/
PROC IMPORT 
DATAFILE ="D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\merged\kitchenall_amazon_4_7_20.csv"
OUT= amazon_kitchnequip3 DBMS=csv
REPLACE;
run;

PROC IMPORT 
DATAFILE ="D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\luggage_boardgamesdata_4_6_2020.csv"
OUT= amazon_luggageboardgames DBMS=csv
REPLACE;
run;
proc contents data = amazon_kitchnequip3;
run;
/**selecting only deep fryer and microwave; water pitcher data not available in the external sites and electric cooker is the focal category**/
data amazon_kitchnequip4 (keep = time1 ASIN seller_ID brand categ Answered_Questions price1 Buy_Box_Price1 Seller_Rating
num_seller_Posrating  num_seller_ratings Product_Star_Rate Sales_Rank_Own_Categ Sales_Rank_Sub_categ 
Product_reviews condition Freeship  FBA Prime Instock season);
set amazon_kitchnequip3;
where categ in ('Deep Fryer', 'Microwave');
if Buy_Box_Price1 = . then Nobuyboxwinner = 1;
else Nobuyboxwinner = 0;
run;
PROC SORT DATA = amazon_kitchnequip4 NODUPKEY OUT= amazon_kitchnequip4;
BY TIME1 ASIN Seller_ID condition ;
RUN;


data amazon_luggageboardgames1 (keep = time1 ASIN seller_ID brand categ Answered_Questions price1 Buy_Box_Price1 Seller_Rating
num_seller_Posrating  num_seller_ratings Product_Star_Rate Sales_Rank_Own_Categ Sales_Rank_Sub_categ 
Product_reviews condition Freeship  FBA Prime Instock season);
set amazon_luggageboardgames;
if Answered_Questions  = . then Answered_Questions  = 0;
run;
/*stacking data sets*/
data amazon_allothers;
set amazon_kitchnequip4 amazon_luggageboardgames1;
run;
proc freq data = amazon_allothers;
tables categ;
run;

/***3P SELLER CHARACTERISTICS***/

/****RESTRICTING DATA TO THE TIME LENGTH EXTERNAL SITE INFORMATION IS AVAILABLE***/

proc freq data = amazon_allothers1;
tables brand;
run;
data amazon_allothers1;
SET amazon_allothers;
where time1 >= '14Dec2017'd and time1 <= '06May2018'd ;
/*length brand1 $50.;*/
/*brand1 = brand;*/
RUN;

/****price change by ASIN***/

/**COMPUTING PRICE CHANGE BY ID, BRAND AND CATEG***/

proc sort data = amazon_allothers1 NODUPkey out=amazon_allothers2 ;
by  ASIN seller_ID time1 ;
run;
proc expand data=amazon_allothers2 out=amazon_allothers3 method = none; 
  by ASIN seller_ID ;
  convert price1 = price_lag1     / transformout=(lag 1); 
RUN;

proc sql;
create table price_changes_amazonothers as
select 
time1,
ASIN,
categ,
brand,
seller_ID,
(case when price_lag1 ne . and price_lag1 ne 0 then (price1-price_lag1)/price_lag1 else 0 end) as pricechange

from
amazon_allothers3
group by
time1,
ASIN,
categ,
brand,
seller_ID;
quit;
/***count number of price changes by seller type and ASIN***/
proc sql;
create table numpricechange_amazonothers  as
select
ASIN,
categ,
brand,
seller_ID,
count(distinct time1) as num_days,
sum(case when pricechange ne 0 then 1 else 0 end) as num_pricechange
from
price_changes_amazonothers
group by
ASIN,
categ,
brand,
seller_ID;
quit;
proc sort data = numpricechange_amazonothers;
by categ;
run;
proc univariate data = numpricechange_amazonothers;
var num_days;
by categ;
/*where categ = 'Microwave';*/
run;

proc freq data =numpricechange_amazonothers;
tables categ;
run;

/**selecting 3P sellers who have been on the marketplace > 10 days**/
DATA REDUCED_SELLERS;
SET numpricechange_amazonothers;
WHERE num_days > 10;
RUN;


proc sort data = REDUCED_SELLERS (keep = ASIN seller_ID) nodupkey out = reduced_seller_list;
by ASIN seller_ID;
run;
proc sort data = amazon_allothers1 out = amazon_allothers_sort;
by ASIN seller_ID;
run;
data amazon_allothers_finalsellers;
merge amazon_allothers_sort (in=a) reduced_seller_list (in=b);
by ASIN seller_ID;
if a and b;
run;

/**classifying used and new product sellers**/

data amazon_allothers_usednew;
set amazon_allothers_finalsellers;
length seller_type $50.;
IF CONDITION = 0 AND SELLER_ID = 'Amazon'  THEN SELLER_TYPE = 'Amazon Old';
ELSE IF CONDITION = 1 AND SELLER_ID = 'Amazon' THEN SELLER_TYPE = 'Amazon New';
ELSE IF CONDITION = 0 AND SELLER_ID NE 'Amazon' THEN SELLER_TYPE = '3P Old';
ELSE IF CONDITION = 1 AND SELLER_ID NE 'Amazon' THEN SELLER_TYPE = '3P New';
run;
proc freq data = amazon_allothers_usednew;
tables condition seller_type;
run;

proc print data = amazon_allothers_usednew (obs=10);
var seller_id;
where seller_type in ( '3P Old','3P New');
run;


/***DETERMINING SELLER CHARACTERISTICS FOR EACH SELLER***/

data amazon_allothers_new;
set amazon_allothers_usednew;
where seller_type = '3P New';
run;
proc freq data = amazon_allothers_usednew;
tables FBA*seller_type /list;
where categ = 'Microwave';
run;

proc sql;
create table SELLER_VARS as
select 
categ,
Seller_ID,
SELLER_TYPE,
count(ASIN) as num_ASIN_sold,
count(distinct ASIN) as num_uniq_ASIN_sold,
mean(condition) as mean_condition,
mean(freeship) as mean_freeship,
mean(FBA) as mean_FBA,
mean(Prime) as mean_prime,
MEAN(seller_rating) as MEAN_SELLER_RATE,
min(seller_rating) as Min_SELLER_RATE,
max(seller_rating) as Max_SELLER_RATE,
MAX(num_seller_Posrating) AS MAX_PERC_POSRATING,
Min(num_seller_Posrating) AS Min_PERC_POSRATING,
Mean(num_seller_Posrating) AS Mean_PERC_POSRATING,
MAX(num_seller_ratings) AS MAX_NUM_RATINGS,
Min(num_seller_ratings) AS Min_NUM_RATINGS,
Mean(num_seller_ratings) AS Mean_NUM_RATINGS,
MEAN(price1) as MEAN_PRICE,
Max(price1) as Max_PRICE,
Min(price1) as Min_PRICE
from 
amazon_allothers_new
group by
categ,
Seller_ID,
SELLER_TYPE
;
quit;

PROC EXPORT 
DATA=SELLER_VARS
DBMS=csv
OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\3psellercharac_4_15_20.csv"
REPLACE;
run;
proc contents data = SELLER_VARS;
run;
proc means data = SELLER_VARS n mean std nway;
class categ;
var 
MEAN_SELLER_RATE
MAX_NUM_RATINGS
MAX_PERC_POSRATING
mean_FBA
mean_freeship
num_uniq_ASIN_sold;
output out = summ_sellercharc ;
run;

proc standard data = SELLER_VARS  mean=0 std=1 out= SELLER_standvars_othercateg;
VAR 
MAX_NUM_RATINGS
MAX_PERC_POSRATING
MEAN_PRICE
MEAN_SELLER_RATE
Max_PRICE
Max_SELLER_RATE
Mean_NUM_RATINGS
Mean_PERC_POSRATING
Min_NUM_RATINGS
Min_PERC_POSRATING
Min_PRICE
Min_SELLER_RATE
mean_FBA
mean_prime
mean_condition
mean_freeship
num_ASIN_sold
num_uniq_ASIN_sold
;
by categ;
RUN;
/*PROC PRINT DATA = SELLER_standvars_Cooker (obs=20);*/
/*RUN;*/

/**RUNNING VARCLUS PROCEDURE TO REMOVE CORRELATED VARIABLES**/
proc varclus data=SELLER_standvars_othercateg    outtree=tree centroid MAXITER = 1000 maxclusters= 40  CENTROID;
var 
Mean_NUM_RATINGS
Mean_PERC_POSRATING
mean_FBA
mean_prime
mean_freeship
num_ASIN_sold
num_uniq_ASIN_sold
;
by categ;
run;

/**RUNNING FASTCLUS PROCEDURE for each category***/

data microwave_clusteranalysis;
set SELLER_standvars_othercateg;
where categ = 'Microwave';
run;

proc fastclus data = microwave_clusteranalysis
 out= sellers_clusters_microwave
 maxc= 10 converge = 0 maxiter=100000 summary;
 var 
Mean_NUM_RATINGS
/*Mean_PERC_POSRATING*/
/*mean_FBA*/
mean_prime
mean_freeship
num_ASIN_sold
/*num_uniq_ASIN_sold*/
;
run;
PROC EXPORT 
DATA=sellers_clusters_microwave
DBMS=csv
OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\clusteroutput_microwave_5_2_20.csv"
RfrigidLACE;
run;

/***clubbing clusters**/
/****CLUBBING CLOSE CLUSTERS TO GET FINAL SET OF CLUSTERS****/

DATA sellers_clusters_microwave1;
SET sellers_clusters_microwave;
IF CLUSTER IN (1,6)  THEN CLUSTER1 = 1;
ELSE IF CLUSTER IN (2,3,4) THEN CLUSTER1 = 2;
ELSE IF CLUSTER in (5,7,9)  THEN CLUSTER1 = 3;
ELSE CLUSTER1 = 4;
RUN;
PROC FREQ DATA = sellers_clusters_microwave1;
TABLES CLUSTER1;
RUN;

/***MERGING CLUSTERS BACK TO THE UN-STANDARDIZED DATA SET TO RECOVER MEAN CLUSTER CHARACTERISTICS****/

PROC SORT DATA = sellers_clusters_microwave1 (KEEP= Seller_ID Cluster1 ) OUT=sellers_clusters_microwave_SORT;
BY Seller_ID;
RUN;
DATA SELLER_VARS_microwave;
SET SELLER_VARS;
WHERE CATEG = 'Microwave';
RUN;

PROC SORT DATA = SELLER_VARS_microwave out=SELLER_VARS_microwave_sort;
BY Seller_ID;
RUN;

data seller_microwave_clusters (drop= cluster1);
merge sellers_clusters_microwave_SORT (in=a) SELLER_VARS_microwave_sort (in=b);
BY Seller_ID;
if a and b;
IF CLUSTER1 = 1 THEN seller_type = 'Cluster 1';
if CLUSTER1 = 2 THEN seller_type = 'Cluster 2';
if CLUSTER1 = 3 THEN seller_type = 'Cluster 3';
if CLUSTER1 = 4 THEN seller_type = 'Cluster 4';
/*if CLUSTER1 = 5 THEN seller_type = 'Cluster 5';*/
run;
PROC CONTENTS DATA = numpricechange_amazonothers;
RUN;
proc means data = seller_microwave_clusters n mean min max std nway ;
class seller_type;
var
MAX_NUM_RATINGS
MAX_PERC_POSRATING
MEAN_SELLER_RATE
Max_SELLER_RATE
Mean_NUM_RATINGS
Mean_PERC_POSRATING
Min_NUM_RATINGS
Min_PERC_POSRATING
Min_PRICE
Min_SELLER_RATE
mean_FBA
mean_prime
mean_condition
mean_freeship
num_ASIN_sold
num_uniq_ASIN_sold;
output out= seller_microwave_cluster_charac ;
run;

PROC EXPORT 
DATA=seller_microwave_cluster_charac
DBMS=csv
OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\clusterPROFILEs_microwave_5_2.csv"
REPLACE;
run;
/***MERGING CLUSTER OUTPUT WITH THE NUMBER OF PRICE CHANGES AND NUMBER OF DAYS TREND***/
data numpricechange_microwave;
set numpricechange_amazonothers;
where categ = 'Microwave';
run;

PROC SORT DATA = numpricechange_microwave OUT= SORTED_PRICECHANGES;
BY SELLER_ID;
RUN;
PROC SORT DATA = seller_microwave_clusters out= sorted_sellerclusters;
by seller_id;
run;
DATA SELLER_PRICECHANGE_microwave;
MERGE SORTED_PRICECHANGES (IN=A) sorted_sellerclusters (IN=B);
BY SELLER_ID;
IF A AND B;
RUN;
proc print data = SELLER_PRICECHANGE_microwave (obs=20);
run;

/***above file exported as PRICECHANGES_SELLERCLUSTER_microwave_4_24.csv****/

/******MERGING CLUSTER OUTPUT WITH THE DAILY PRICE TREND DATA AT ASIN LEVEL***/
/**/
/*proc freq data = amazon_allothers_usednew;*/
/*tables seller_type;*/
/*run;*/
/*proc contents data = amazon_allothers_usednew;*/
/*run;*/
data microwave_usednew;
set amazon_allothers_usednew;
where categ = 'Microwave';
run;

proc sql;
create table price_dev_BB as
select 
time1,
ASIN,
Seller_ID,
(price1-Buy_Box_Price1)as dev_BBprice

from 
microwave_usednew
group by
time1,
ASIN,
Seller_ID
;
quit;

/***ADDED VARIABLES AFTER CONVO WITH BRYAN --- NEED TO INCLUDE THESE IN THE SELLER ENTRY AND BUY BOX MODELS***/
/**SELLER ENTRY: NON BUY BOX 3P PRICE; BUY BOX MODEL: DIFFERENCE FROM LOWEST PRICE OFFER***/
proc sql;
   create table lowestprice_offer as
   select 
      time1,
      ASIN,
/*      Buy_Box_Price1,*/
      min(price1) as lowestprice_offer
   from microwave_usednew
   group by time1, ASIN;
quit;
proc print data = lowestprice_offer (obs=20);
run;

data non_buybox3pprice;
set microwave_usednew;
where seller_id ne 'Amazon' and price1 ne Buy_Box_Price1;
run;
proc sql;
   create table mean_nonbuybox3pprice as
   select 
      time1,
      ASIN,
      mean(price1) as nonbuybox3p_price
   from non_buybox3pprice
   group by time1, ASIN;
quit;
proc sort data = mean_nonbuybox3pprice;
by time1 ASIN;
run;

proc sort data = microwave_usednew out = focal_rival_prods26_sort;
by time1 ASIN;
run;

data focal_rival_prods27;
merge focal_rival_prods26_sort (in=a) mean_nonbuybox3pprice (in=b) ;
by time1 ASIN;
if a and b ;
run;
proc freq data = focal_rival_prods27;
tables asin*brand / list;
run;
/*proc freq data = microwave_usednew;*/
/*tables asin*brand / list;*/
/*run;*/
proc means data = focal_rival_prods27 n sum nway noprint;
class time1 ASIN seller_id seller_type;
var price1 Buy_Box_Price1 Seller_Rating num_seller_Posrating  num_seller_ratings condition Freeship  FBA Prime Instock ;
output out = price_trend sum= ;
run;
PROC SORT DATA = price_trend OUT= price_trend1;
BY SELLER_ID ;
RUN;
data price_trend2 (drop=seller_type);
set price_trend1;
where seller_type = '3P New';
run;
data price_trend3;
set price_trend1;
where seller_type ne '3P New';
run;

PROC SORT DATA = seller_microwave_clusters (KEEP= SELLER_ID seller_type) OUT= SELLER_TYPE1;
BY SELLER_ID ;
RUN;

DATA newSELLER_TYPE_TREND ;
MERGE SELLER_TYPE1 (IN=A) price_trend2 (IN=B);
BY SELLER_ID ;
IF B;
RUN;
/*proc print data = newSELLER_TYPE_TREND (obs=20);*/
/*run; */
data SELLER_TYPE_TREND;
set newSELLER_TYPE_TREND price_trend3;
run;
/*proc freq data = SELLER_TYPE_TREND;*/
/*tables seller_type;*/
/*run;*/


PROC SORT DATA = SELLER_TYPE_TREND;
BY TIME1 ASIN;
RUN;

DATA SELLER_TYPE_TREND1;
MERGE SELLER_TYPE_TREND (IN=A) lowestprice_offer (IN=B);
BY TIME1 ASIN;
IF A ;
RUN;
/****EXTRA SUMMARY NEEDED FOR DEVIATION FROM BUY BOX PRICE - THIS VARIABLE IS NOT INCLUDED IN THE FINALDATA USED FOR MODELING**/
proc sort data = price_dev_BB;
by time1 asin seller_id;
run;
proc sort data = SELLER_TYPE_TREND1;
by time1 asin seller_id;
run;

data devbuybox_MWAVE;
merge seller_type_trend1 (in=a) price_dev_bb (in=b);
by time1 asin seller_id;
if a and b;
run;
proc print data = devbuybox_MWAVE (obs=10);
/*var nonbuybox3p_price;*/
/*where ASIN = 'B00NQ7QFGM';*/
run;
PROC MEANS DATA = devbuybox_MWAVE N SUM MEAN MAX MIN NWAY;
CLASS SELLER_TYPE;
VAR dev_BBprice;
OUTPUT OUT = DEV_SUMMARY;
RUN;

/*****************************************/

/*PROC EXPORT */
/*DATA=SELLER_TYPE_TREND*/
/*DBMS=csv*/
/*OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\Sellerclusters_microwave_May2020.csv"*/
/*RfrigidLACE;*/
/*run;*/
PROC EXPORT 
DATA=SELLER_TYPE_TREND1
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\Sellerclusters_microwave_July2025.csv"
REPLACE;
run;
/**COMPUTING PRICE CHANGE BY ASIN AND SELLER TYPE***/

proc sort data = SELLER_TYPE_TREND1 NODUPREC out=SELLER_TYPE_TREND11 ;
by  ASIN seller_type seller_ID TIME1 ;
run;
proc expand data=SELLER_TYPE_TREND11 out=SELLER_TYPE_TREND12 method = none; 
  by ASIN seller_type seller_ID;
  convert price1 = price_lag1     / transformout=(lag 1); 
RUN;

proc sql;
create table price_changes_sellertype as
select 
time1,
ASIN,
seller_type,
seller_ID,
price1,
(price1 - lowestprice_offer) as pricediff_fromlowest,
(case when price_lag1 ne . and price_lag1 ne 0 then (price1-price_lag1)/price_lag1 else 0 end) as pricechange
from
SELLER_TYPE_TREND12
group by
time1,
ASIN,
seller_type,
seller_ID;
quit;
/*PROC EXPORT */
/*DATA=price_changes_sellertype*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\price_changes_microwave_May2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=price_changes_sellertype
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\price_changes_microwave_July2025.csv"
REPLACE;
run;

/***count number of price changes by seller type and ASIN***/

proc sql;
create table num_pricechange_sellertype_asin as
select
seller_ID, 
seller_type,
ASIN,
count(distinct time1) as num_days,
sum(case when pricechange ne 0 then 1 else 0 end) as num_pricechange
from
price_changes_sellertype
group by
seller_ID, 
seller_type,
ASIN;
quit;
/*PROC EXPORT */
/*DATA=num_pricechange_sellertype_asin*/
/*DBMS=csv*/
/*OUTFILE= "C:\Users\F00456N\Documents\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\num_pricechange_sellertype_asin_microwave_May2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=num_pricechange_sellertype_asin
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\num_pricechange_sellertype_asin_microwave_July2025.csv"
REPLACE;
run;

/***selecting the representative seller and ASIN per cluster with most number of price changes in the period***/

proc sort data = num_pricechange_sellertype_asin out= sorted_bymostchanges ;
by seller_type descending num_pricechange descending num_days ASIN;
run;

/*PROC EXPORT */
/*DATA=sorted_bymostchanges*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\sorted_bymostchanges_microwave_May2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=sorted_bymostchanges
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\sorted_bymostchanges_microwave_July2025.csv"
REPLACE;
run;
data new3psellers;
set sorted_bymostchanges;
/*where seller_type in ('Cluster 1', 'Cluster 2', 'Cluster 3', 'Cluster 4', 'Cluster 5');*/
where seller_type in ('Cluster 1', 'Cluster 2', 'Cluster 3', 'Cluster 4') ;
run;
proc sort data = new3psellers nodupkey out= new3p_pricechange_vector;
by seller_type ASIN;
run;
proc freq data = new3p_pricechange_vector;
tables ASIN*seller_type /list;
run;

data amazon;
set sorted_bymostchanges;
where seller_type in ('Amazon New') and ASIN IN ( 'B00KNUHBJU', 'B004VEP2NQ', 'B01DEWZWDU', 'B01DEWZWGC');
run;


data newandamazonsellers;
set new3p_pricechange_vector amazon;
run;

/**merging back to the price changes main file to get the trend for the chosen seller IDs and ASINs**/
/*Amazon New	B00KNUHBJU	Panasonic*/
/*Amazon New	B004VEP2NQ	LG*/
/*Cluster 1	B01M7RABCO	Kenmore*/
/*Cluster 2	B01DEWZWDU	Panasonic*/
/*Cluster 3	B01M7RABCO	Kenmore*/
/*Cluster 4	B01DEWZWGC	Panasonic*/
/*Cluster 4	B004VEP2NQ	LG*/

proc sort data = price_changes_sellertype out= price_changes_sellertype1;
by seller_type seller_ID ASIN;
run;
proc sort data = newandamazonsellers out= newandamazonsellers1;
by seller_type seller_ID ASIN;
run;
data pricechangetrend_outcome;
merge newandamazonsellers1 (in=a) price_changes_sellertype1 (in=b);
by seller_type seller_ID ASIN;
if a and b;
run;
proc freq data = new3p_pricechange_vector;
tables ASIN*seller_type /list;
run;

proc sort data = pricechangetrend_outcome (keep=time1) nodupkey out= final_outputvector;
by time1;
run;
%macro pricechange(a,b,c);
data pricechange&b. (keep = time1 pricechange_&b. price&b. pricediff_lowst_&b.);
set pricechangetrend_outcome;
where seller_type = &a. and ASIN = &c.;
rename pricechange = pricechange_&b. ;
rename price1 = price&b.;
rename pricediff_fromlowest = pricediff_lowst_&b.;
run;

data final_outputvector ;
merge final_outputvector (in=a) pricechange&b. (in=b);
by time1;
if a ;
if a and not b then pricechange_&b. = 0;
run;
%mend;
%pricechange(a='Amazon New', b = Amzn_psonic, c = 'B00KNUHBJU'); 
%pricechange(a='Amazon New', b = Amzn_psonic2, c = 'B01DEWZWDU'); 
%pricechange(a='Amazon New', b = Amzn_psonic4, c = 'B01DEWZWGC'); 
%pricechange(a='Amazon New', b = Amzn_lg, c = 'B004VEP2NQ'); 
%pricechange(a='Cluster 1', b = clust1_knmore, c = 'B01M7RABCO'); 
%pricechange(a='Cluster 2', b = clust2_psonic, c = 'B01DEWZWDU'); 
%pricechange(a='Cluster 3', b = clust3_knmore, c = 'B01M7RABCO'); 
%pricechange(a='Cluster 4', b = clust4_psonic, c = 'B01DEWZWGC'); 
%pricechange(a='Cluster 4', b = clust4_lg, c = 'B004VEP2NQ');
/*%prodchar(a= 'B00KNUHBJU', b = psonic);*/
/*%prodchar(a= 'B01DEWZWDU', b = psonic2);*/
/*%prodchar(a= 'B01DEWZWGC', b = psonic4);*/

/*PROC EXPORT */
/*DATA=final_outputvector*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\pricechangetrend_microwave_May2020.csv"*/
/*REPLACE;*/
/*run;*/
/*PROC EXPORT */
/*DATA=final_outputvector*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\pricechangetrend_microwave_July2025.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=final_outputvector
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\pricechangetrend_microwave_Nov2025.csv"
REPLACE;
run;

/***CLUSTER MEMBERSHIP: NUMBER OF SELLERS IN A CLUSTER AT TIME T, BRANDS SOLD BY SELLER TYPES AT TIME T,
NUMBER OF PRICE CHANGES BY OWN AND CROSS BRANDS BY SELLER TYPES AT TIME (T-1), (T-2), (T-3), *******/
/*Amazon New	B00KNUHBJU	Panasonic*/
/*Amazon New	B004VEP2NQ	LG*/
/*Cluster 1	B01M7RABCO	Kenmore*/
/*Cluster 2	B01DEWZWDU	Panasonic*/
/*Cluster 3	B01M7RABCO	Kenmore*/
/*Cluster 4	B01DEWZWGC	Panasonic*/

PROC SQL;
CREATE TABLE SELLER_offercharcs AS
SELECT
TIME1,
SELLER_TYPE,
COUNT(DISTINCT SELLER_ID) AS NUM_SELLERS,
COUNT(DISTINCT ASIN) AS NUM_SKUS,
sum(case when ASIN in ('B00KNUHBJU','B01DEWZWDU' , 'B01DEWZWGC') then 1 else 0 end) AS NUM_psonic,
sum(case when ASIN = 'B004VEP2NQ' then 1 else 0 end) as num_lg, 
/*sum(case when ASIN = 'B015TBCZ4I' then 1 else 0 end) as num_frigid,*/
sum(case when ASIN = 'B01M7RABCO' then 1 else 0 end) as num_knmore
from 
SELLER_TYPE_TREND12
group by
time1,
seller_type;
quit;

proc sort data = SELLER_offercharcs (keep = time1) nodupkey out= SELLER_offercharcs_base;
by time1;
run;

%macro seller_offercharacs(a,b);
data seller_offer&a.;
set SELLER_offercharcs;
where seller_type = &b.;
rename NUM_SELLERS = NUM_SELLERS&a.;
rename NUM_SKUS = NUM_SKUS&a.;
rename NUM_psonic = NUM_psonic&a.;
rename num_lg = num_lg&a.;
/*rename num_frigid = num_frigid&a.;*/
rename num_knmore = num_knmore&a.;
run;

proc sort data = seller_offer&a. (drop=  seller_type) out= sorted_seller_type;
by time1;
run;

data SELLER_offercharcs_base;
merge SELLER_offercharcs_base (in=a) sorted_seller_type (in=b);
by time1;
if a ;
if a and not b then do;
NUM_SELLERS&a. = 0;
NUM_SKUS&a. = 0;
NUM_psonic&a.=0;
num_lg&a.= 0;
/*num_frigid&a. = 0;*/
num_knmore&a. = 0;
end;
run;
%mend;
%seller_offercharacs(a = used3p, b = '3P Old');
%seller_offercharacs(a = usedamzn, b = 'Amazon Old'); 
%seller_offercharacs(a = amzn, b = 'Amazon New');
%seller_offercharacs(a = clust1, b = 'Cluster 1'); 
%seller_offercharacs(a = clust2, b = 'Cluster 2'); 
%seller_offercharacs(a = clust3, b = 'Cluster 3');
%seller_offercharacs(a = clust4, b = 'Cluster 4');
/*%seller_offercharacs(a = clust5, b = 'Cluster 5'); */

/*****COMPETITOR PRICING INFORMATION*****/
/**ACCOUNTING FOR PRICE CHANGES IN EACH SELLER TYPE BY BRANDS****/

proc sql;
create table num_pricechange_sellertype_asin as
select
TIME1,
seller_type,
ASIN,
sum(case when pricechange ne 0 then 1 else 0 end) as num_pricechange
from
price_changes_sellertype
group by
time1,
seller_type,
ASIN;
quit;


proc sort data = num_pricechange_sellertype_asin (keep = time1)nodupkey out= numpricechange_sellerbrandtrend;
by time1;
run;
/*Amazon New	B00KNUHBJU	Panasonic*/
/*Amazon New	B004VEP2NQ	LG*/
/*Cluster 1	B01M7RABCO	Kenmore*/
/*Cluster 2	B01DEWZWDU	Panasonic*/
/*Cluster 3	B01M7RABCO	Kenmore*/
/*Cluster 4	B01DEWZWGC	Panasonic*/
%macro seller_brand(a,b,c,d);
data num_pricechange_&d.&b.;
set num_pricechange_sellertype_asin;
where ASIN in &c. and seller_type = &a.;
rename num_pricechange = num_pricechange&b.&d.;
run;

proc sql;
create table num_pricechange1_&d.&b. as
select 
time1,
sum(num_pricechange&b.&d.) as num_pricechange1&b.&d.
from 
num_pricechange_&d.&b.
group by
time1;
quit;

proc sort data = num_pricechange1_&d.&b. NODUPKEY out= sorted;
by time1;
run;

data numpricechange_sellerbrandtrend;
merge numpricechange_sellerbrandtrend (in=a) sorted (in=b);
by time1;
if a ;
if a and not b then num_pricechange1&b.&d. = 0;
run;
%mend;

%seller_brand(a='3P Old',b= psonic, c =('B00KNUHBJU','B01DEWZWDU' , 'B01DEWZWGC'), d= used3p);
%seller_brand(a='Amazon Old',b= psonic, c=('B00KNUHBJU','B01DEWZWDU' , 'B01DEWZWGC'), d= usedamzn);
%seller_brand(a='Amazon New',b= psonic, c=('B00KNUHBJU','B01DEWZWDU' , 'B01DEWZWGC'), d= amzn);
%seller_brand(a='Cluster 1',b= psonic, c=('B00KNUHBJU','B01DEWZWDU' , 'B01DEWZWGC'), d= clust1);
%seller_brand(a='Cluster 2',b= psonic, c=('B00KNUHBJU','B01DEWZWDU' , 'B01DEWZWGC'), d= clust2);
%seller_brand(a='Cluster 3',b= psonic, c=('B00KNUHBJU','B01DEWZWDU' , 'B01DEWZWGC'), d= clust3);
%seller_brand(a='Cluster 4',b= psonic, c=('B00KNUHBJU','B01DEWZWDU' , 'B01DEWZWGC'), d= clust4);

%seller_brand(a='3P Old',b= lg, c =('B004VEP2NQ'), d= used3p);
%seller_brand(a='Amazon Old',b= lg, c=('B004VEP2NQ'), d= usedamzn);
%seller_brand(a='Amazon New',b= lg, c=('B004VEP2NQ'), d= amzn);
%seller_brand(a='Cluster 1',b= lg, c=('B004VEP2NQ'), d= clust1);
%seller_brand(a='Cluster 2',b= lg, c=('B004VEP2NQ'), d= clust2);
%seller_brand(a='Cluster 3',b= lg, c=('B004VEP2NQ'), d= clust3);
%seller_brand(a='Cluster 4',b= lg, c=('B004VEP2NQ'), d= clust4);

/*%seller_brand(a='3P Old',b= frigid, c =('B015TBCZ4I'), d= used3p);*/
/*%seller_brand(a='Amazon Old',b= frigid, c=('B015TBCZ4I'), d= usedamzn);*/
/*%seller_brand(a='Amazon New',b= frigid, c=('B015TBCZ4I'), d= amzn);*/
/*%seller_brand(a='Cluster 1',b= frigid, c=('B015TBCZ4I'), d= clust1);*/
/*%seller_brand(a='Cluster 2',b= frigid, c=('B015TBCZ4I'), d= clust2);*/
/*%seller_brand(a='Cluster 3',b= frigid, c=('B015TBCZ4I'), d= clust3);*/
/*%seller_brand(a='Cluster 4',b= frigid, c=('B015TBCZ4I'), d= clust4);*/

%seller_brand(a='3P Old',b= knmore, c =('B01M7RABCO'), d= used3p);
%seller_brand(a='Amazon Old',b= knmore, c=('B01M7RABCO'), d= usedamzn);
%seller_brand(a='Amazon New',b= knmore, c=('B01M7RABCO'), d= amzn);
%seller_brand(a='Cluster 1',b= knmore, c=('B01M7RABCO'), d= clust1);
%seller_brand(a='Cluster 2',b= knmore, c=('B01M7RABCO'), d= clust2);
%seller_brand(a='Cluster 3',b= knmore, c=('B01M7RABCO'), d= clust3);
%seller_brand(a='Cluster 4',b= knmore, c=('B01M7RABCO'), d= clust4);

proc print data = numpricechange_sellerbrandtrend (obs=20);
run;

/****************************/
/*****SELLER ATTRIBUTES******/
/****************************/
/***EXTRACTING NUM OF UNIQUE ASINS AND NUM OF ASINS SOLD CONDITION FOR EACH SELLER***/
PROC SQL;
create table seller_asin as
select
time1,
seller_ID,
count(distinct ASIN) as unique_ASIN_sold,
count(distinct time1) as sell_freq
from
SELLER_TYPE_TREND12
group by
time1,
seller_ID
;
quit;
/**MERGING BACK TO THE MAIN DATA SET**/
PROC SORT DATA = SELLER_TYPE_TREND12 OUT= SELLER_TYPE_TREND4;
BY time1 seller_ID;
RUN;
PROC SORT DATA = seller_asin OUT= seller_asin1;
BY time1 seller_ID;
RUN;
DATA SELLER_TYPE_TREND5;
MERGE SELLER_TYPE_TREND4 (IN=A) seller_asin1 (IN=B);
BY time1 seller_ID;
IF A AND B ;
RUN;

/**creating cumulative sell freq over time***/
proc sort data = SELLER_TYPE_TREND5 (keep= time1 seller_ID sell_freq) nodupkey out=SELLER_TYPE_TREND6;
by seller_ID time1;
run;

data cum_sellfreq;
  set SELLER_TYPE_TREND6;
  by seller_ID ;
  if first.seller_ID then cum_sellerfreq = 0;
  cum_sellerfreq +sell_freq;
run;
/**merging back to main data set**/
PROC SORT DATA = SELLER_TYPE_TREND5 OUT= SELLER_TYPE_TREND7;
BY time1 seller_ID;
RUN;
PROC SORT DATA = cum_sellfreq OUT= cum_sellfreq1;
BY time1 seller_ID;
RUN;
DATA SELLER_TYPE_TREND8;
MERGE SELLER_TYPE_TREND7 (IN=A) cum_sellfreq1 (IN=B);
BY time1 seller_ID;
IF A AND B ;
RUN;

PROC SQL;
create table SELLER_CHAR as
select
time1,
seller_type,
SUM(FBA) AS NUM_FBA,
sum(prime) as num_prime,
SUM(FREESHIP) AS NUM_FREESHIP,
SUM(CONDITION) AS NUM_NEWOFFERS,
sum(case when condition = 0 then 1 else 0 end) AS NUM_USEDOFFERS,
max(num_seller_Posrating) as max_perct_sellerrating,
mean(num_seller_Posrating) as mean_perct_sellerrating,
min(num_seller_Posrating) as min_perct_sellerrating,
max(num_seller_ratings) as max_num_seller_rating,
mean(num_seller_ratings) as mean_num_seller_rating,
min(num_seller_ratings) as min_num_seller_rating,
max(seller_rating) as max_seller_star_rating,
mean(seller_rating) as mean_seller_star_rating,
min(seller_rating) as min_seller_star_rating,
max(unique_ASIN_sold) as max_uniq_ASIN,
mean(unique_ASIN_sold) as mean_uniq_ASIN,
min(unique_ASIN_sold) as min_uniq_ASIN,
max(cum_sellerfreq) as max_cumsalefreq,
mean(cum_sellerfreq) as mean_cumsalefreq,
min(cum_sellerfreq) as min_cumsalefreq
from
SELLER_TYPE_TREND8
group by
time1,
seller_type
;
quit;

/**rename each seller char to individual columns for each seller cluster for model build, i.e., max_fba_clust1, min_fba_clust1 etc. ****/

proc sort data = SELLER_CHAR  (keep=time1) nodupkey out=seller_char_sorted;
by time1;
run;
data sellerchar_amzn;
set SELLER_CHAR;
where seller_type = 'Amazon New';
rename num_Freeship = Freeship_amzn;
rename num_prime = prime_amzn;
rename max_cumsalefreq = cumsalefreq_amzn;
run;
data sellerchar_usedamzn;
set SELLER_CHAR;
where seller_type = 'Amazon Old';
rename num_Freeship = Freeship_usedamzn;
run;
data sellerchar_used3p;
set SELLER_CHAR;
where seller_type = '3P Old';
rename num_FBA = FBA_used3p;
rename num_prime = prime_used3p;
rename num_Freeship = Freeship_used3p;
run;
proc sort data = sellerchar_amzn (keep = time1 Freeship_amzn prime_amzn cumsalefreq_amzn) out= sellerchar_amzn1;
by time1;
run;

proc sort data = sellerchar_usedamzn (keep = time1 Freeship_usedamzn  ) out= sellerchar_amznused1;
by time1;
run;
proc sort data = sellerchar_used3p (keep = time1 FBA_used3p Freeship_used3p prime_used3p ) out= sellerchar_used3p1;
by time1;
run;
data seller_char_sorted ;
merge seller_char_sorted (in=a) sellerchar_amzn1 (in=b) sellerchar_amznused1 (in =c) sellerchar_used3p1 (in=d);
by time1;
if a ;
if  a and not b then do;
Freeship_amzn = 0;
cumsalefreq_amzn = 0;
prime_amzn = 0;
end;
if a and not c then do;
Freeship_usedamzn = 0;
end;
if a and not d then do;
FBA_used3p = 0;
Freeship_used3p = 0;
prime_used3p = 0;
end;
run;

%macro sellerchar(a,b);
data sellerchar_&b.;
set SELLER_CHAR;
where seller_type = &a.;
rename num_FBA = num_FBA_&b.;
rename num_prime = num_prime_&b.;
rename num_Freeship = num_Freeship_&b.;
rename max_perct_sellerrating = max_perct_sellerrating_&b.;
rename mean_perct_sellerrating = mean_perct_sellerrating_&b.;
rename min_perct_sellerrating = min_perct_sellerrating_&b.;
rename max_num_seller_rating = max_num_seller_rating_&b.;
rename mean_num_seller_rating = mean_num_seller_rating_&b.;
rename min_num_seller_rating = min_num_seller_rating_&b.;
rename max_seller_star_rating = max_seller_star_rating_&b.;
rename mean_seller_star_rating = mean_seller_star_rating_&b.;
rename min_seller_star_rating = min_seller_star_rating_&b.;
rename max_uniq_ASIN = max_uniq_ASIN_&b.;
rename mean_uniq_ASIN = mean_uniq_ASIN_&b.;
rename min_uniq_ASIN = min_uniq_ASIN_&b.;
rename max_cumsalefreq = max_cumsalefreq_&b.;
rename mean_cumsalefreq = mean_cumsalefreq_&b.;
rename min_cumsalefreq = min_cumsalefreq_&b.;
run;

proc sort data = sellerchar_&b. (keep = time1 num_FBA_&b. num_prime_&b. num_Freeship_&b. max_perct_sellerrating_&b. 
mean_perct_sellerrating_&b. min_perct_sellerrating_&b. max_num_seller_rating_&b. mean_num_seller_rating_&b.
min_num_seller_rating_&b. max_seller_star_rating_&b. mean_seller_star_rating_&b. min_seller_star_rating_&b. 
max_uniq_ASIN_&b. mean_uniq_ASIN_&b. min_uniq_ASIN_&b. max_cumsalefreq_&b. mean_cumsalefreq_&b. 
min_cumsalefreq_&b.) out= sellerchar_sorted&b.;
by time1;
run;

data seller_char_sorted ;
merge seller_char_sorted (in=a) sellerchar_sorted&b. (in=b);
by time1;
if a ;
if a and not b then do;
num_FBA_&b. = 0;
num_prime_&b.=0;
num_Freeship_&b. = 0;
max_perct_sellerrating_&b. = 0;
mean_perct_sellerrating_&b. = 0;
min_perct_sellerrating_&b. = 0;
max_num_seller_rating_&b. = 0;
mean_num_seller_rating_&b. = 0;
min_num_seller_rating_&b. = 0;
max_seller_star_rating_&b. = 0;
mean_seller_star_rating_&b. = 0;
min_seller_star_rating_&b. = 0;
max_uniq_ASIN_&b. = 0;
mean_uniq_ASIN_&b. = 0;
min_uniq_ASIN_&b. = 0;
max_cumsalefreq_&b. = 0;
mean_cumsalefreq_&b. = 0;
min_cumsalefreq_&b. = 0;
end;
run;
%mend;
%sellerchar(a='Cluster 1', b = clust1); 
%sellerchar(a='Cluster 2', b = clust2); 
%sellerchar(a='Cluster 3', b = clust3); 
%sellerchar(a='Cluster 4', b = clust4); 

/***********************************/
/*************BRAND ATTRIBUTES*****/
/*********************************/
/**SUMMARIZING PRODUCT CHAR FOR psonic***/
proc contents data = microwave_usednew;
run;
PROC SQL;
create table PRODUCT_CHAR_psonic as
select
time1,
MAX(Answered_Questions) as max_ans_questions_psonic,
mean(Answered_Questions) as mean_ans_questions_psonic,
min(Answered_Questions) as min_ans_questions_psonic,
max(Buy_Box_Price1) as max_buyboxprice_psonic,
mean(Buy_Box_Price1) as mean_buyboxprice_psonic,
min(Buy_Box_Price1) as min_buyboxprice_psonic,
max(nonbuybox3p_price) as max_nonbbox3pprice_psonic,
mean(nonbuybox3p_price) as mean_nonbbox3pprice_psonic,
min(nonbuybox3p_price) as min_nonbbox3pprice_psonic,
max(Product_Star_Rate) as max_product_star_rate_psonic,
mean(Product_Star_Rate) as mean_product_star_rate_psonic,
min(Product_Star_Rate) as min_product_star_rate_psonic,
max(Product_reviews) as max_Product_reviews_psonic,
mean(Product_reviews) as mean_Product_reviews_psonic,
min(Product_reviews) as min_Product_reviews_psonic,
max(Sales_Rank_Own_Categ) as max_salesrank_categ_psonic,
mean(Sales_Rank_Own_Categ) as mean_salesrank_categ_psonic,
min(Sales_Rank_Own_Categ) as min_salesrank_categ_psonic,
max(Sales_Rank_Sub_categ) as max_salesrank_subcateg_psonic,
mean(Sales_Rank_Sub_categ) as mean_salesrank_subcateg_psonic,
min(Sales_Rank_Sub_categ) as min_salesrank_subcateg_psonic
from
focal_rival_prods27
WHERE ASIN IN ('B00KNUHBJU','B01DEWZWDU','B01DEWZWGC')
group by
time1;
quit;

/**MERGING BACK TO THE SELLER CHARACTERISTICS DATA***/
DATA seller_char_sorted;
MERGE seller_char_sorted (IN=A) PRODUCT_CHAR_psonic (IN=B) SELLER_offercharcs_base (in=c);
BY TIME1;
IF A ;
IF A AND NOT B THEN DO;
max_ans_questions_psonic =0;
mean_ans_questions_psonic =0;
min_ans_questions_psonic =0;
max_buyboxprice_psonic =0;
mean_buyboxprice_psonic =0;
min_buyboxprice_psonic =0;
max_nonbbox3pprice_psonic =0;
mean_nonbbox3pprice_psonic =0;
min_nonbbox3pprice_psonic =0;
max_product_star_rate_psonic =0;
mean_product_star_rate_psonic =0;
min_product_star_rate_psonic =0;
max_Product_reviews_psonic =0;
mean_Product_reviews_psonic =0;
min_Product_reviews_psonic =0;
max_salesrank_categ_psonic =0;
mean_salesrank_categ_psonic =0;
min_salesrank_categ_psonic =0;
max_salesrank_subcateg_psonic =0;
mean_salesrank_subcateg_psonic =0;
min_salesrank_subcateg_psonic =0;
END;
RUN;


proc sort data = seller_char_sorted out= ALL_char_sorted ;
by time1;
run;
%macro prodchar(a,b);
data prodchar_&b.;
set focal_rival_prods27;
where ASIN = &a.;
rename Answered_Questions = Answered_Questions&b. ;
rename Buy_Box_Price1 = buyboxprice_&b.;
rename nonbuybox3p_price = nonbbox3pprice_&b.;
rename Product_Star_Rate = product_star_&b.;
rename Product_reviews = Product_reviews_&b.;
rename Sales_Rank_Own_Categ = Sales_Rank_Own_Categ_&b.;
rename Sales_Rank_Sub_categ = Sales_Rank_Sub_categ_&b.;
run;

proc sort data = prodchar_&b. (keep = time1  Answered_Questions&b. buyboxprice_&b. nonbbox3pprice_&b. product_star_&b. 
Product_reviews_&b. Sales_Rank_Own_Categ_&b. Sales_Rank_Sub_categ_&b.) NODUPKEY out= prodchar_sorted&b.;
by time1;
run;

data ALL_char_sorted ;
merge ALL_char_sorted (in=a) prodchar_sorted&b. (in=b);
by time1;
if a;
IF A AND NOT B THEN DO;
Answered_Questions&b. = 0;
buyboxprice_&b. = 0;
nonbbox3pprice_&b. = 0;
product_star_&b. = 0;
Product_reviews_&b. = 0;
Sales_Rank_Own_Categ_&b. = 0;
Sales_Rank_Sub_categ_&b. = 0;
END;
run;
%mend;
%prodchar(a= 'B004VEP2NQ', b = lg); 
/*%prodchar(a= 'B015TBCZ4I', b = frigid);*/
%prodchar(a= 'B01M7RABCO', b = knmore);
%prodchar(a= 'B00KNUHBJU', b = psonic);
%prodchar(a= 'B01DEWZWDU', b = psonic2);
%prodchar(a= 'B01DEWZWGC', b = psonic4);

/*Amazon New	B00KNUHBJU	Panasonic*/
/*Amazon New	B004VEP2NQ	LG*/
/*Cluster 1	B01M7RABCO	Kenmore*/
/*Cluster 2	B01DEWZWDU	Panasonic*/
/*Cluster 3	B01M7RABCO	Kenmore*/
/*Cluster 4	B01DEWZWGC	Panasonic*/

/**MERGING WITH BRAND SELLER PRICE CHANGE TREND**/
DATA ALL_COVARIATES ;
MERGE ALL_char_sorted (IN = A) numpricechange_sellerbrandtrend (IN=B) ;
BY TIME1;
IF A ;
RUN;
proc contents data = final_outputvector;
run;
/*****merging with final outcome vector****/

proc sort data = final_outputvector ;
by time1;
run;

data merge_finaldata;
merge final_outputvector (in=a) ALL_COVARIATES (in=b);
by time1;
if a;
rename pricechange_Amzn_psonic = Y_amzn_psonic;
rename pricechange_Amzn_psonic2 = Y_amzn_psonic2;
rename pricechange_Amzn_psonic4 = Y_amzn_psonic4;
rename pricechange_Amzn_lg = Y_amzn_lg;
rename pricechange_clust1_knmore = Y_clust1_knmore;
rename pricechange_clust2_psonic = Y_clust2_psonic;
rename pricechange_clust3_knmore = Y_clust3_knmore;
rename pricechange_clust4_psonic = Y_clust4_psonic;
rename pricechange_clust4_lg = Y_clust4_lg;
run;

proc contents data = merge_finaldata;
run;
proc sort data = merge_finaldata NODUPREC out=merge_finaldata1 ;
by  TIME1 ;
run;


proc expand data=merge_finaldata1 out=merge_finaldata2 method = none; 
convert	Y_amzn_lg	=	Y_amzn_lg_lag1	/transformout=(lag 1);
convert	Y_amzn_lg	=	Y_amzn_lg_lag2	/transformout=(lag 2);
convert	Y_amzn_lg	=	Y_amzn_lg_lag3	/transformout=(lag 3);
convert	Y_amzn_psonic	=	Y_amzn_psonic_lag1	/transformout=(lag 1);
convert	Y_amzn_psonic	=	Y_amzn_psonic_lag2	/transformout=(lag 2);
convert	Y_amzn_psonic	=	Y_amzn_psonic_lag3	/transformout=(lag 3);

convert	Y_amzn_psonic2	=	Y_amzn_psonic2_lag1	/transformout=(lag 1);
convert	Y_amzn_psonic2	=	Y_amzn_psonic2_lag2	/transformout=(lag 2);
convert	Y_amzn_psonic2	=	Y_amzn_psonic2_lag3	/transformout=(lag 3);

convert	Y_amzn_psonic4	=	Y_amzn_psonic4_lag1	/transformout=(lag 1);
convert	Y_amzn_psonic4	=	Y_amzn_psonic4_lag2	/transformout=(lag 2);
convert	Y_amzn_psonic4	=	Y_amzn_psonic4_lag3	/transformout=(lag 3);

convert	Y_clust1_knmore	=	Y_clust1_knmore_lag1	/transformout=(lag 1);
convert	Y_clust1_knmore	=	Y_clust1_knmore_lag2	/transformout=(lag 2);
convert	Y_clust1_knmore	=	Y_clust1_knmore_lag3	/transformout=(lag 3);
convert	Y_clust2_psonic	=	Y_clust2_psonic_lag1	/transformout=(lag 1);
convert	Y_clust2_psonic	=	Y_clust2_psonic_lag2	/transformout=(lag 2);
convert	Y_clust2_psonic	=	Y_clust2_psonic_lag3	/transformout=(lag 3);
convert	Y_clust3_knmore	=	Y_clust3_knmore_lag1	/transformout=(lag 1);
convert	Y_clust3_knmore	=	Y_clust3_knmore_lag2	/transformout=(lag 2);
convert	Y_clust3_knmore	=	Y_clust3_knmore_lag3	/transformout=(lag 3);
convert	Y_clust4_psonic	=	Y_clust4_psonic_lag1	/transformout=(lag 1);
convert	Y_clust4_psonic	=	Y_clust4_psonic_lag2	/transformout=(lag 2);
convert	Y_clust4_psonic	=	Y_clust4_psonic_lag3	/transformout=(lag 3);

convert	Y_clust4_lg	=	Y_clust4_lg_lag1	/transformout=(lag 1);
convert	Y_clust4_lg	=	Y_clust4_lg_lag2	/transformout=(lag 2);
convert	Y_clust4_lg	=	Y_clust4_lg_lag3	/transformout=(lag 3);

/*convert	Answered_Questionsfrigid	=	Answered_Questionsfrigid_lag1	/transformout=(lag 1);*/
convert	Answered_Questionsknmore	=	Answered_Questionsknmore_lag1	/transformout=(lag 1);
convert	Answered_Questionslg	=	Answered_Questionslg_lag1	/transformout=(lag 1);
convert	FBA_used3p	=	FBA_used3p_lag1	/transformout=(lag 1);
convert	Freeship_amzn	=	Freeship_amzn_lag1	/transformout=(lag 1);
convert	Freeship_used3p	=	Freeship_used3p_lag1	/transformout=(lag 1);
convert	Freeship_usedamzn	=	Freeship_usedamzn_lag1	/transformout=(lag 1);
convert	NUM_SELLERSamzn	=	NUM_SELLERSamzn_lag1	/transformout=(lag 1);
convert	NUM_SELLERSclust1	=	NUM_SELLERSclust1_lag1	/transformout=(lag 1);
convert	NUM_SELLERSclust2	=	NUM_SELLERSclust2_lag1	/transformout=(lag 1);
convert	NUM_SELLERSclust3	=	NUM_SELLERSclust3_lag1	/transformout=(lag 1);
convert	NUM_SELLERSclust4	=	NUM_SELLERSclust4_lag1	/transformout=(lag 1);
convert	NUM_SELLERSused3p	=	NUM_SELLERSused3p_lag1	/transformout=(lag 1);
convert	NUM_SELLERSusedamzn	=	NUM_SELLERSusedamzn_lag1	/transformout=(lag 1);
convert	NUM_SKUSamzn	=	NUM_SKUSamzn_lag1	/transformout=(lag 1);
convert	NUM_SKUSclust1	=	NUM_SKUSclust1_lag1	/transformout=(lag 1);
convert	NUM_SKUSclust2	=	NUM_SKUSclust2_lag1	/transformout=(lag 1);
convert	NUM_SKUSclust3	=	NUM_SKUSclust3_lag1	/transformout=(lag 1);
convert	NUM_SKUSclust4	=	NUM_SKUSclust4_lag1	/transformout=(lag 1);
convert	NUM_SKUSused3p	=	NUM_SKUSused3p_lag1	/transformout=(lag 1);
convert	NUM_SKUSusedamzn	=	NUM_SKUSusedamzn_lag1	/transformout=(lag 1);
convert	NUM_psonicamzn	=	NUM_psonicamzn_lag1	/transformout=(lag 1);
convert	NUM_psonicclust1	=	NUM_psonicclust1_lag1	/transformout=(lag 1);
convert	NUM_psonicclust2	=	NUM_psonicclust2_lag1	/transformout=(lag 1);
convert	NUM_psonicclust3	=	NUM_psonicclust3_lag1	/transformout=(lag 1);
convert	NUM_psonicclust4	=	NUM_psonicclust4_lag1	/transformout=(lag 1);
convert	NUM_psonicused3p	=	NUM_psonicused3p_lag1	/transformout=(lag 1);
convert	NUM_psonicusedamzn	=	NUM_psonicusedamzn_lag1	/transformout=(lag 1);
/*convert	Product_reviews_frigid	=	Product_reviews_frigid_lag1	/transformout=(lag 1);*/
convert	Product_reviews_knmore	=	Product_reviews_knmore_lag1	/transformout=(lag 1);
convert	Product_reviews_lg	=	Product_reviews_lg_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_knmore	=	salerank_knmore_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_lg	=	salerank_lg_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_psonic	=	salerank_psonic_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_psonic2	=	salerank_psonic2_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_psonic4	=	salerank_psonic4_lag1	/transformout=(lag 1);
/*convert	Sales_Rank_Sub_categ_frigid	=	salerank_subcat_frigid_lag1	/transformout=(lag 1);*/
convert	Sales_Rank_Sub_categ_knmore	=	salerank_subcat_knmore_lag1	/transformout=(lag 1);
convert	Sales_Rank_Sub_categ_lg	=	salerank_subcat_lg_lag1	/transformout=(lag 1);
convert	buyboxprice_knmore	=	buyboxprice_knmore_lag1	/transformout=(lag 1);
convert	buyboxprice_lg	=	buyboxprice_lg_lag1	/transformout=(lag 1);
convert	buyboxprice_psonic	=	buyboxprice_psonic_lag1	/transformout=(lag 1);
convert	buyboxprice_psonic2	=	buyboxprice_psonic2_lag1	/transformout=(lag 1);
convert	buyboxprice_psonic4	=	buyboxprice_psonic4_lag1	/transformout=(lag 1);
/*convert nonbbox3pprice_EP =   nonbbox3pprice_frigid_lag  /transformout=(lag 1);*/
convert nonbbox3pprice_knmore =   nonbbox3pprice_knmore_lag  /transformout=(lag 1);
convert nonbbox3pprice_lg =   nonbbox3pprice_lg_lag  /transformout=(lag 1);
convert	cumsalefreq_amzn	=	cumsalefreq_amzn_lag1	/transformout=(lag 1);
convert	max_Product_reviews_psonic	=	maxProduct_reviews_psonic_lag1	/transformout=(lag 1);
convert	max_ans_questions_psonic	=	maxans_questions_psonic_lag1	/transformout=(lag 1);
convert	max_buyboxprice_psonic	=	maxbuyboxprice_psonic_lag1	/transformout=(lag 1);
convert	max_cumsalefreq_clust1	=	maxcumsalefreq_clust1_lag1	/transformout=(lag 1);
convert	max_cumsalefreq_clust2	=	maxcumsalefreq_clust2_lag1	/transformout=(lag 1);
convert	max_cumsalefreq_clust3	=	maxcumsalefreq_clust3_lag1	/transformout=(lag 1);
convert	max_cumsalefreq_clust4	=	maxcumsalefreq_clust4_lag1	/transformout=(lag 1);
convert	max_num_seller_rating_clust1	=	maxnum_sellerate_clust1_lag1	/transformout=(lag 1);
convert	max_num_seller_rating_clust2	=	maxnum_sellerate_clust2_lag1	/transformout=(lag 1);
convert	max_num_seller_rating_clust3	=	maxnum_sellerate_clust3_lag1	/transformout=(lag 1);
convert	max_num_seller_rating_clust4	=	maxnum_sellerate_clust4_lag1	/transformout=(lag 1);
convert	max_perct_sellerrating_clust1	=	maxperct_sellerate_clust1_lag1	/transformout=(lag 1);
convert	max_perct_sellerrating_clust2	=	maxperct_sellerate_clust2_lag1	/transformout=(lag 1);
convert	max_perct_sellerrating_clust3	=	maxperct_sellerate_clust3_lag1	/transformout=(lag 1);
convert	max_perct_sellerrating_clust4	=	maxperct_sellerate_clust4_lag1	/transformout=(lag 1);
convert	max_product_star_rate_psonic	=	maxprodstar_psonic_lag1	/transformout=(lag 1);
convert	max_salesrank_categ_psonic	=	maxsalesrank_categ_psonic_lag1	/transformout=(lag 1);
convert	max_salesrank_subcateg_psonic	=	maxsalesrank_subcat_psonic_lag1	/transformout=(lag 1);
convert	max_seller_star_rating_clust1	=	maxseller_star_clust1_lag1	/transformout=(lag 1);
convert	max_seller_star_rating_clust2	=	maxseller_star_clust2_lag1	/transformout=(lag 1);
convert	max_seller_star_rating_clust3	=	maxseller_star_clust3_lag1	/transformout=(lag 1);
convert	max_seller_star_rating_clust4	=	maxseller_star_clust4_lag1	/transformout=(lag 1);
convert	max_uniq_ASIN_clust1	=	maxuniq_ASIN_clust1_lag1	/transformout=(lag 1);
convert	max_uniq_ASIN_clust2	=	maxuniq_ASIN_clust2_lag1	/transformout=(lag 1);
convert	max_uniq_ASIN_clust3	=	maxuniq_ASIN_clust3_lag1	/transformout=(lag 1);
convert	max_uniq_ASIN_clust4	=	maxuniq_ASIN_clust4_lag1	/transformout=(lag 1);
convert	mean_Product_reviews_psonic	=	meanProduct_reviews_psonic_lag1	/transformout=(lag 1);
convert	mean_ans_questions_psonic	=	meanans_questions_psonic_lag1	/transformout=(lag 1);
convert	mean_buyboxprice_psonic	=	meanbuyboxprice_psonic_lag1	/transformout=(lag 1);
convert	mean_nonbbox3pprice_psonic	=	mean_nonbbox3pprice_psonic_lag	/transformout=(lag 1);
convert	mean_cumsalefreq_clust1	=	meancumsalefreq_clust1_lag1	/transformout=(lag 1);
convert	mean_cumsalefreq_clust2	=	meancumsalefreq_clust2_lag1	/transformout=(lag 1);
convert	mean_cumsalefreq_clust3	=	meancumsalefreq_clust3_lag1	/transformout=(lag 1);
convert	mean_cumsalefreq_clust4	=	meancumsalefreq_clust4_lag1	/transformout=(lag 1);
convert	mean_num_seller_rating_clust1	=	meannum_sellerate_clust1_lag1	/transformout=(lag 1);
convert	mean_num_seller_rating_clust2	=	meannum_sellerate_clust2_lag1	/transformout=(lag 1);
convert	mean_num_seller_rating_clust3	=	meannum_sellerate_clust3_lag1	/transformout=(lag 1);
convert	mean_num_seller_rating_clust4	=	meannum_sellerate_clust4_lag1	/transformout=(lag 1);
convert	mean_perct_sellerrating_clust1	=	meanperct_sellerate_clust1_lag1	/transformout=(lag 1);
convert	mean_perct_sellerrating_clust2	=	meanperct_sellerate_clust2_lag1	/transformout=(lag 1);
convert	mean_perct_sellerrating_clust3	=	meanperct_sellerate_clust3_lag1	/transformout=(lag 1);
convert	mean_perct_sellerrating_clust4	=	meanperct_sellerate_clust4_lag1	/transformout=(lag 1);
convert	mean_product_star_rate_psonic	=	meanprodstar_psonic_lag1	/transformout=(lag 1);
convert	mean_salesrank_categ_psonic	=	meansalesrank_categ_psonic_lag1	/transformout=(lag 1);
convert	mean_salesrank_subcateg_psonic	=	meansalesrank_subcat_psonic_lag1	/transformout=(lag 1);
convert	mean_seller_star_rating_clust1	=	meanseller_star_clust1_lag1	/transformout=(lag 1);
convert	mean_seller_star_rating_clust2	=	meanseller_star_clust2_lag1	/transformout=(lag 1);
convert	mean_seller_star_rating_clust3	=	meanseller_star_clust3_lag1	/transformout=(lag 1);
convert	mean_seller_star_rating_clust4	=	meanseller_star_clust4_lag1	/transformout=(lag 1);
convert	mean_uniq_ASIN_clust1	=	meanuniq_ASIN_clust1_lag1	/transformout=(lag 1);
convert	mean_uniq_ASIN_clust2	=	meanuniq_ASIN_clust2_lag1	/transformout=(lag 1);
convert	mean_uniq_ASIN_clust3	=	meanuniq_ASIN_clust3_lag1	/transformout=(lag 1);
convert	mean_uniq_ASIN_clust4	=	meanuniq_ASIN_clust4_lag1	/transformout=(lag 1);
convert	min_Product_reviews_psonic	=	minProduct_reviews_psonic_lag1	/transformout=(lag 1);
convert	min_ans_questions_psonic	=	minans_questions_psonic_lag1	/transformout=(lag 1);
convert	min_buyboxprice_psonic	=	minbuyboxprice_psonic_lag1	/transformout=(lag 1);
convert	min_cumsalefreq_clust1	=	mincumsalefreq_clust1_lag1	/transformout=(lag 1);
convert	min_cumsalefreq_clust2	=	mincumsalefreq_clust2_lag1	/transformout=(lag 1);
convert	min_cumsalefreq_clust3	=	mincumsalefreq_clust3_lag1	/transformout=(lag 1);
convert	min_cumsalefreq_clust4	=	mincumsalefreq_clust4_lag1	/transformout=(lag 1);
convert	min_num_seller_rating_clust1	=	minnum_sellerate_clust1_lag1	/transformout=(lag 1);
convert	min_num_seller_rating_clust2	=	minnum_sellerate_clust2_lag1	/transformout=(lag 1);
convert	min_num_seller_rating_clust3	=	minnum_sellerate_clust3_lag1	/transformout=(lag 1);
convert	min_num_seller_rating_clust4	=	minnum_sellerate_clust4_lag1	/transformout=(lag 1);
convert	min_perct_sellerrating_clust1	=	minperct_sellerate_clust1_lag1	/transformout=(lag 1);
convert	min_perct_sellerrating_clust2	=	minperct_sellerate_clust2_lag1	/transformout=(lag 1);
convert	min_perct_sellerrating_clust3	=	minperct_sellerate_clust3_lag1	/transformout=(lag 1);
convert	min_perct_sellerrating_clust4	=	minperct_sellerate_clust4_lag1	/transformout=(lag 1);
convert	min_product_star_rate_psonic	=	minprodstar_psonic_lag1	/transformout=(lag 1);
convert	min_salesrank_categ_psonic	=	minsalesrank_categ_psonic_lag1	/transformout=(lag 1);
convert	min_salesrank_subcateg_psonic	=	minsalesrank_subcat_psonic_lag1	/transformout=(lag 1);
convert	min_seller_star_rating_clust1	=	minseller_star_clust1_lag1	/transformout=(lag 1);
convert	min_seller_star_rating_clust2	=	minseller_star_clust2_lag1	/transformout=(lag 1);
convert	min_seller_star_rating_clust3	=	minseller_star_clust3_lag1	/transformout=(lag 1);
convert	min_seller_star_rating_clust4	=	minseller_star_clust4_lag1	/transformout=(lag 1);
convert	min_uniq_ASIN_clust1	=	minuniq_ASIN_clust1_lag1	/transformout=(lag 1);
convert	min_uniq_ASIN_clust2	=	minuniq_ASIN_clust2_lag1	/transformout=(lag 1);
convert	min_uniq_ASIN_clust3	=	minuniq_ASIN_clust3_lag1	/transformout=(lag 1);
convert	min_uniq_ASIN_clust4	=	minuniq_ASIN_clust4_lag1	/transformout=(lag 1);
convert	num_FBA_clust1	=	num_FBA_clust1_lag1	/transformout=(lag 1);
convert	num_FBA_clust2	=	num_FBA_clust2_lag1	/transformout=(lag 1);
convert	num_FBA_clust3	=	num_FBA_clust3_lag1	/transformout=(lag 1);
convert	num_FBA_clust4	=	num_FBA_clust4_lag1	/transformout=(lag 1);
convert	num_Freeship_clust1	=	num_Freeship_clust1_lag1	/transformout=(lag 1);
convert	num_Freeship_clust2	=	num_Freeship_clust2_lag1	/transformout=(lag 1);
convert	num_Freeship_clust3	=	num_Freeship_clust3_lag1	/transformout=(lag 1);
convert	num_Freeship_clust4	=	num_Freeship_clust4_lag1	/transformout=(lag 1);
convert	num_knmoreamzn	=	num_knmoreamzn_lag1	/transformout=(lag 1);
convert	num_knmoreclust1	=	num_knmoreclust1_lag1	/transformout=(lag 1);
convert	num_knmoreclust2	=	num_knmoreclust2_lag1	/transformout=(lag 1);
convert	num_knmoreclust3	=	num_knmoreclust3_lag1	/transformout=(lag 1);
convert	num_knmoreclust4	=	num_knmoreclust4_lag1	/transformout=(lag 1);
convert	num_knmoreused3p	=	num_knmoreused3p_lag1	/transformout=(lag 1);
convert	num_knmoreusedamzn	=	num_knmoreusedamzn_lag1	/transformout=(lag 1);
convert	num_lgamzn	=	num_lgamzn_lag1	/transformout=(lag 1);
convert	num_lgclust1	=	num_lgclust1_lag1	/transformout=(lag 1);
convert	num_lgclust2	=	num_lgclust2_lag1	/transformout=(lag 1);
convert	num_lgclust3	=	num_lgclust3_lag1	/transformout=(lag 1);
convert	num_lgclust4	=	num_lgclust4_lag1	/transformout=(lag 1);
convert	num_lgused3p	=	num_lgused3p_lag1	/transformout=(lag 1);
convert	num_lgusedamzn	=	num_lgusedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1knmoreamzn	=	num_prchngknmoreamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1knmoreclust1	=	num_prchngknmoreclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1knmoreclust2	=	num_prchngknmoreclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1knmoreclust3	=	num_prchngknmoreclust3_lag1	/transformout=(lag 1);
convert	num_pricechange1knmoreclust4	=	num_prchngknmoreclust4_lag1	/transformout=(lag 1);
convert	num_pricechange1knmoreused3p	=	num_prchngknmoreused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1knmoreusedamzn	=	num_prchngknmoreusedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1lgamzn	=	num_prchnglgamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1lgclust1	=	num_prchnglgclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1lgclust2	=	num_prchnglgclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1lgclust3	=	num_prchnglgclust3_lag1	/transformout=(lag 1);
convert	num_pricechange1lgclust4	=	num_prchnglgclust4_lag1	/transformout=(lag 1);
convert	num_pricechange1lgused3p	=	num_prchnglgused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1lgusedamzn	=	num_prchnglgusedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1psonicamzn	=	num_prchngpsonicamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1psonicclust1	=	num_prchngpsonicclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1psonicclust2	=	num_prchngpsonicclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1psonicclust3	=	num_prchngpsonicclust3_lag1	/transformout=(lag 1);
convert	num_pricechange1psonicclust4	=	num_prchngpsonicclust4_lag1	/transformout=(lag 1);
convert	num_pricechange1psonicused3p	=	num_prchngpsonicused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1psonicusedamzn	=	num_prchngpsonicusedamzn_lag1	/transformout=(lag 1);
convert	num_prime_clust1	=	num_prime_clust1_lag1	/transformout=(lag 1);
convert	num_prime_clust2	=	num_prime_clust2_lag1	/transformout=(lag 1);
convert	num_prime_clust3	=	num_prime_clust3_lag1	/transformout=(lag 1);
convert	num_prime_clust4	=	num_prime_clust4_lag1	/transformout=(lag 1);
convert	priceAmzn_lg	=	priceAmzn_lg_lag	/transformout = (lag 1);
convert	priceAmzn_psonic	=	priceAmzn_psonic_lag	/transformout = (lag 1);
convert	priceAmzn_psonic2	=	priceAmzn_psonic2_lag	/transformout = (lag 1);
convert	priceAmzn_psonic4	=	priceAmzn_psonic4_lag	/transformout = (lag 1);
convert	priceclust1_knmore	=	priceclust1_knmore_lag	/transformout = (lag 1);
convert	priceclust2_psonic	=	priceclust2_psonic_lag	/transformout = (lag 1);
convert	priceclust3_knmore	=	priceclust3_knmore_lag	/transformout = (lag 1);
convert	priceclust4_psonic	=	priceclust4_psonic_lag	/transformout = (lag 1);
convert	priceclust4_lg	=	priceclust4_lg_lag	/transformout = (lag 1);
convert	pricediff_lowst_Amzn_lg	=	pricediff_lowst_Amzn_lg_lag	/transformout = (lag 1);
convert	pricediff_lowst_Amzn_psonic	=	pricediff_lowst_Amzn_psonic_lag	/transformout = (lag 1);
convert	pricediff_lowst_Amzn_psonic2	=	pricediff_lowst_Amzn_psonic2_lag	/transformout = (lag 1);
convert	pricediff_lowst_Amzn_psonic4	=	pricediff_lowst_Amzn_psonic4_lag	/transformout = (lag 1);
convert	pricediff_lowst_clust1_knmore	=	pricedifflowst_clust1knm_lag	/transformout = (lag 1);
convert	pricediff_lowst_clust2_psonic	=	pricedifflowst_clust2psn_lag	/transformout = (lag 1);
convert	pricediff_lowst_clust3_knmore	=	pricedifflowst_clust3knm_lag	/transformout = (lag 1);
convert	pricediff_lowst_clust4_psonic	=	pricedifflowst_clust4psn_lag	/transformout = (lag 1);
convert	pricediff_lowst_clust4_lg	=	pricedifflowst_clust4lg_lag	/transformout = (lag 1);
convert	prime_amzn	=	prime_amzn_lag1	/transformout=(lag 1);
convert	prime_used3p	=	prime_used3p_lag1	/transformout=(lag 1);
/*convert	product_star_frigid	=	product_star_frigid_lag1	/transformout=(lag 1);*/
convert	product_star_knmore	=	product_star_knmore_lag1	/transformout=(lag 1);
convert	product_star_lg	=	product_star_lg_lag1	/transformout=(lag 1);

run;



/**merging back seasonality indicator to the modeling data ***/

proc sort data = amazon_kitchnequip3 (keep = time1 season) nodupkey out= seasonality_data;
by time1;
run;
data mvrf_model1;
merge merge_finaldata2 (in=a) seasonality_data (in=b);
by time1 ;
if a and b;
run;

/***ADDING External site data*****/

PROC import 
DATAfile ="D:\Amazon Price Dynamics\New Data\Data Scraped\homedepot_merged\HD_Pricechangesallcateg_4-6-20.csv" 
DBMS=csv
OUT= HD_pricechange_allcateg
REPLACE;
run;

PROC import 
DATAfile ="D:\Amazon Price Dynamics\New Data\Data Scraped\Walmart_merged\walmart_Pricechangesallcateg_4-6-20.csv" 
DBMS=csv
OUT= wm_pricechange_allcateg
REPLACE;
run;

PROC import 
DATAfile ="D:\Amazon Price Dynamics\New Data\Data Scraped\Sears_merged\Sears_Pricechangesallcateg_4-6-20.csv" 
DBMS=csv
OUT= sears_pricechange_allcateg
REPLACE;
run;
data sears_pricechange_allcateg;
set sears_pricechange_allcateg;
rename date = time1;
run;
proc sql;
create table  HD as
select 
time1,
categ,
max(pricechange) as max_pricechange_HD,
mean(pricechange) as mean_pricechange_HD,
min(pricechange) as min_pricechange_HD
from
HD_pricechange_allcateg
where time1 >= '14Dec2017'd and time1 <= '06May2018'd 
group by
time1,
categ;
quit;

data HD1;
set HD;
if abs(min_pricechange_HD) > abs(max_pricechange_HD) then maxpricechange_HD = min_pricechange_HD;
else if abs(min_pricechange_HD) < abs(max_pricechange_HD) then maxpricechange_HD = max_pricechange_HD;
else maxpricechange_HD = 0;
run;
proc sql;
create table WM as
select 
time1,
categ,
max(pricechange) as max_pricechange_wm,
mean(pricechange) as mean_pricechange_wm,
min(pricechange) as min_pricechange_wm
from
wm_pricechange_allcateg
where time1 >= '14Dec2017'd and time1 <= '06May2018'd 
group by
time1,
categ;
quit;
data wm1;
set wm;
if abs(min_pricechange_wm) > abs(max_pricechange_wm) then maxpricechange_wm = min_pricechange_wm;
else if abs(min_pricechange_wm) < abs(max_pricechange_wm) then maxpricechange_wm = max_pricechange_wm;
else maxpricechange_wm = 0;
run;
proc freq data = wm1;
tables time1;run;

proc sql;
create table Sears as
select 
time1,
categ,
max(pricechange) as max_pricechange_sears,
mean(pricechange) as mean_pricechange_sears,
min(pricechange) as min_pricechange_sears
from
sears_pricechange_allcateg
where time1 >= '14Dec2017'd and time1 <= '06May2018'd 
group by
time1,
categ;
quit;
data sears1;
set sears;
if abs(min_pricechange_sears) > abs(max_pricechange_sears) then maxpricechange_sears = min_pricechange_sears;
else if abs(min_pricechange_sears) < abs(max_pricechange_sears) then maxpricechange_sears = max_pricechange_sears;
else maxpricechange_sears = 0;
run;

/*data merged_allwebsites (drop= min_pricechange_HD max_pricechange_HD min_pricechange_wm max_pricechange_wm*/
/*min_pricechange_sears max_pricechange_sears);*/
/*merge HD1 (in=a) WM1 (in=b) Sears1 (in=c);*/
/*by time1 categ;*/
/*if a and b and c;*/
/*if abs(maxpricechange_HD)= max(abs(maxpricechange_WM),abs(maxpricechange_HD),abs(maxpricechange_sears)) then  maxpricechange_externalsite = maxpricechange_HD;*/
/*else if abs(maxpricechange_WM) = max(abs(maxpricechange_WM),abs(maxpricechange_HD),abs(maxpricechange_sears)) then maxpricechange_externalsite = maxpricechange_WM;*/
/*else if abs(maxpricechange_sears) = max(abs(maxpricechange_WM),abs(maxpricechange_HD),abs(maxpricechange_sears)) then maxpricechange_externalsite = maxpricechange_sears;*/
/*else maxpricechange_externalsite = 0;*/
/*meanpricechange_externalsite = mean(mean_pricechange_HD, mean_pricechange_WM,mean_pricechange_sears);*/
/*run;*/

data merged_allwebsites (drop= min_pricechange_HD max_pricechange_HD min_pricechange_wm max_pricechange_wm
min_pricechange_sears max_pricechange_sears);
merge HD1 (in=a) WM1 (in=b) Sears1 (in=c);
by time1 categ;
if a and b and c;
run;

/**export this above file for all other categories**/
/**extracting only Deep fryer categ***/

data external_microwaveprices;
set merged_allwebsites;
where categ = 'Microwave';
run;

/**/
/*proc sort data = external_microwaveprices (drop = maxpricechange_HD mean_pricechange_HD */
/*maxpricechange_WM mean_pricechange_WM */
/*maxpricechange_sears mean_pricechange_sears categ)*/
/*out=externalsite_data2;*/
/*by time1;*/
/*run;*/
/**/
/*proc expand data=externalsite_data2 out=externalsite_data3 method = none; */
/*convert	maxpricechange_externalsite	=	maxpricechange_extsite_lag1	/transformout=(lag 1);*/
/*convert	meanpricechange_externalsite	=	meanpricechange_extsite_lag1	/transformout=(lag 1);*/
/*run;*/
/*data externalsite_data4 (drop= maxpricechange_externalsite meanpricechange_externalsite);*/
/*set externalsite_data3;*/
/*run;*/
/**/
/*proc sort data = mvrf_model1;*/
/*by time1;*/
/*run;*/
/**/
/*data mvrfdata_withexternalsite;*/
/*merge mvrf_model1 (in=a) externalsite_data4 (in=b);*/
/*by time1;*/
/*if a;*/
/*if a and not b then do;*/
/*externalsite_ind = 0;*/
/*maxpricechange_extsite_lag1 = 0;*/
/*meanpricechange_extsite_lag1 = 0;*/
/*end;*/
/*else externalsite_ind = 1;*/
/*run;*/
/*proc contents data = mvrfdata_withexternalsite;*/
/*run;*/
/**/
/*PROC EXPORT */
/*DATA=mvrfdata_withexternalsite*/
/*DBMS=csv*/
/*OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\mvrfdata_withexternalsite_microwave_May2020.csv"*/
/*REPLACE;*/
/*run;*/


/***importing data for work post JM RnR -- ROUND 4***/
/**/
/*PROC import */
/*DATAFILE ="D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\mvrfdata_withexternalsite_microwave_May2020.csv"*/
/*DBMS=csv*/
/*OUT= mvrfdata_withexternalsite*/
/*REPLACE;*/
/*run;*/

proc contents data = mvrf_model1;
run;
/**merging with edited external data ***/

proc sort data = mvrf_model1;
by time1;
run;

proc sort data = external_microwaveprices (drop = categ) out = externalsite_data2;
by time1;
run;
data mvrfdata_withexternalsite1;
merge mvrf_model1 (in=a) externalsite_data2 (in=b);
by time1;
if a;
if a and not b then do;
ext_site = 0;
maxpricechange_HD = 0;   
maxpricechange_sears = 0;     
maxpricechange_wm = 0;     
mean_pricechange_HD = 0;     
mean_pricechange_sears = 0;     
mean_pricechange_wm = 0;
end;
else ext_site =1;
run;
/***lagging external site price changes****/ 
data mvrfdata_withexternalsite2;
set mvrfdata_withexternalsite1;
maxpricechange_HDlag1 = lag(maxpricechange_HD);   
maxpricechange_searslag1 = lag(maxpricechange_sears);   
maxpricechange_wmlag1 = lag(maxpricechange_wm);
maxpricechange_HDlag2 = lag2(maxpricechange_HD);   
maxpricechange_searslag2 = lag2(maxpricechange_sears);   
maxpricechange_wmlag2 = lag2(maxpricechange_wm);
maxpricechange_HDlag3 = lag3(maxpricechange_HD);   
maxpricechange_searslag3 = lag3(maxpricechange_sears);   
maxpricechange_wmlag3 = lag3(maxpricechange_wm);
run;

proc freq data = mvrfdata_withexternalsite1;
tables ext_site;
run;
/***DAY OF THE WEEK IDENTIFICATION**/

data mvrfdata_withexternalsite3;
set mvrfdata_withexternalsite2;
day_week = weekday(time1);
if day_week = 1 then sunday = 1; else sunday = 0;
if day_week = 2 then monday = 1; else monday = 0;
if day_week = 3 then tuesday = 1; else tuesday = 0;
if day_week = 4 then wednesday = 1; else wednesday = 0;
if day_week = 5 then thursday = 1; else thursday = 0;
if day_week = 6 then friday = 1; else friday = 0;
if day_week = 7 then saturday = 1; else saturday = 0;
if day_week in (1,7) then weekend = 1; else weekend = 0;
run;

/*PROC UNIVARIATE DATA = mvrfdata_withexternalsite1 ;*/
/*VAR */
/*maxsalesrank_categ_psonic_lag1*/
/*meansalesrank_categ_psonic_lag1*/
/*minsalesrank_categ_psonic_lag1*/
/*salerank_frigid_lag1*/
/*salerank_knmore_lag1*/
/*salerank_lg_lag1*/
/**/
/*;*/
/*run;*/

data mvrfdata_withexternalsite4;
set mvrfdata_withexternalsite3;
top_salesrank = min(ifn(maxsalesrank_categ_psonic_lag1=0,.,maxsalesrank_categ_psonic_lag1),
                    ifn(meansalesrank_categ_psonic_lag1=0,.,meansalesrank_categ_psonic_lag1),
                    ifn(minsalesrank_categ_psonic_lag1=0,.,minsalesrank_categ_psonic_lag1), 
/*                    ifn(salerank_frigid_lag1=0,.,salerank_frigid_lag1),*/
                    ifn(salerank_knmore_lag1=0,.,salerank_knmore_lag1),
					ifn(salerank_lg_lag1=0,.,salerank_lg_lag1));

bottom_salesrank = max(ifn(maxsalesrank_categ_psonic_lag1=0,.,maxsalesrank_categ_psonic_lag1),
                    ifn(meansalesrank_categ_psonic_lag1=0,.,meansalesrank_categ_psonic_lag1),
                    ifn(minsalesrank_categ_psonic_lag1=0,.,minsalesrank_categ_psonic_lag1), 
/*                    ifn(salerank_frigid_lag1=0,.,salerank_frigid_lag1),*/
                    ifn(salerank_knmore_lag1=0,.,salerank_knmore_lag1),
					ifn(salerank_lg_lag1=0,.,salerank_lg_lag1));

run;


data mvrfdata_withexternalsite5;
set mvrfdata_withexternalsite4;

if top_salesrank ne . and top_salesrank ne bottom_salesrank then do;
if minsalesrank_categ_psonic_lag1 = top_salesrank then psonic_topbrandlag1 = 1; else psonic_topbrandlag1 = 0; 
/*if salerank_frigid_lag1 = top_salesrank then frigid_topbrandlag1 = 1; else frigid_topbrandlag1 = 0; */
if salerank_knmore_lag1 = top_salesrank then knmore_topbrandlag1 = 1; else knmore_topbrandlag1 = 0; 
if salerank_lg_lag1 = top_salesrank then lg_topbrandlag1 = 1; else lg_topbrandlag1 = 0; 
end;

if bottom_salesrank ne . and top_salesrank ne bottom_salesrank then do;
if minsalesrank_categ_psonic_lag1 = bottom_salesrank then psonic_bottombrandlag1 = 1; else psonic_bottombrandlag1 = 0; 
/*if salerank_frigid_lag1 = bottom_salesrank then frigid_bottombrandlag1 = 1; else frigid_bottombrandlag1 = 0; */
if salerank_knmore_lag1 = bottom_salesrank then knmore_bottombrandlag1 = 1; else knmore_bottombrandlag1 = 0; 
if salerank_lg_lag1 = bottom_salesrank then lg_bottombrandlag1 = 1; else lg_bottombrandlag1 = 0; 
end;

else do;
psonic_topbrandlag1 = 0;
/*frigid_topbrandlag1 = 0; */
knmore_topbrandlag1 = 0; 
lg_topbrandlag1 = 0; 
psonic_bottombrandlag1 = 0; 
/*frigid_bottombrandlag1 = 0; */
knmore_bottombrandlag1 = 0; 
lg_bottombrandlag1 = 0;
end;

run;



/***creating main effect of price change**/
data final_gam_modeldata;
set mvrfdata_withexternalsite5;

/*price increase/decrease of 5% and -5%, with direction**/
/**LG**/
if Y_amzn_lg_lag1 >= 0.05 and Y_amzn_lg_lag1 < 0.1 then incr5per_amzn_lg = 1; else incr5per_amzn_lg = 0;
if Y_amzn_lg_lag1 <= -0.05 and Y_amzn_lg_lag1 > -0.1 then decr5per_amzn_lg = 1; else decr5per_amzn_lg = 0;

if Y_amzn_lg_lag1 >= 0.1 and Y_amzn_lg_lag1 < 0.2 then incr10per_amzn_lg = 1; else incr10per_amzn_lg = 0;
if Y_amzn_lg_lag1 <= -0.1 and Y_amzn_lg_lag1 > -0.2 then decr10per_amzn_lg = 1; else decr10per_amzn_lg = 0;

if Y_amzn_lg_lag1 >= 0.2 then incr20per_amzn_lg = 1; else incr20per_amzn_lg = 0;
if Y_amzn_lg_lag1 <= -0.2 then decr20per_amzn_lg = 1; else decr20per_amzn_lg = 0;


/**Panasonic**/
if Y_amzn_psonic_lag1 >= 0.05 and Y_amzn_psonic_lag1 < 0.1 then incr5per_amzn_psonic = 1; else incr5per_amzn_psonic = 0;
if Y_amzn_psonic_lag1 <= -0.05 and Y_amzn_psonic_lag1 > -0.1 then decr5per_amzn_psonic = 1; else decr5per_amzn_psonic = 0;

if Y_amzn_psonic_lag1 >= 0.1 and Y_amzn_psonic_lag1 < 0.2 then incr10per_amzn_psonic = 1; else incr10per_amzn_psonic = 0;
if Y_amzn_psonic_lag1 <= -0.1 and Y_amzn_psonic_lag1 > -0.2 then decr10per_amzn_psonic = 1; else decr10per_amzn_psonic = 0;

if Y_amzn_psonic_lag1 >= 0.2 then incr20per_amzn_psonic = 1; else incr20per_amzn_psonic = 0;
if Y_amzn_psonic_lag1 <= -0.2 then decr20per_amzn_psonic = 1; else decr20per_amzn_psonic = 0;

if Y_amzn_psonic2_lag1 >= 0.05 and Y_amzn_psonic2_lag1 < 0.1 then incr5per_amzn_psonic2 = 1; else incr5per_amzn_psonic2 = 0;
if Y_amzn_psonic2_lag1 <= -0.05 and Y_amzn_psonic2_lag1 > -0.1 then decr5per_amzn_psonic2 = 1; else decr5per_amzn_psonic2 = 0;

if Y_amzn_psonic2_lag1 >= 0.1 and Y_amzn_psonic2_lag1 < 0.2 then incr10per_amzn_psonic2 = 1; else incr10per_amzn_psonic2 = 0;
if Y_amzn_psonic2_lag1 <= -0.1 and Y_amzn_psonic2_lag1 > -0.2 then decr10per_amzn_psonic2 = 1; else decr10per_amzn_psonic2 = 0;

if Y_amzn_psonic2_lag1 >= 0.2 then incr20per_amzn_psonic2 = 1; else incr20per_amzn_psonic2 = 0;
if Y_amzn_psonic2_lag1 <= -0.2 then decr20per_amzn_psonic2 = 1; else decr20per_amzn_psonic2 = 0;

if Y_amzn_psonic4_lag1 >= 0.05 and Y_amzn_psonic4_lag1 < 0.1 then incr5per_amzn_psonic4 = 1; else incr5per_amzn_psonic4 = 0;
if Y_amzn_psonic4_lag1 <= -0.05 and Y_amzn_psonic4_lag1 > -0.1 then decr5per_amzn_psonic4 = 1; else decr5per_amzn_psonic4 = 0;

if Y_amzn_psonic4_lag1 >= 0.1 and Y_amzn_psonic4_lag1 < 0.2 then incr10per_amzn_psonic4 = 1; else incr10per_amzn_psonic4 = 0;
if Y_amzn_psonic4_lag1 <= -0.1 and Y_amzn_psonic4_lag1 > -0.2 then decr10per_amzn_psonic4 = 1; else decr10per_amzn_psonic4 = 0;

if Y_amzn_psonic4_lag1 >= 0.2 then incr20per_amzn_psonic4 = 1; else incr20per_amzn_psonic4 = 0;
if Y_amzn_psonic4_lag1 <= -0.2 then decr20per_amzn_psonic4 = 1; else decr20per_amzn_psonic4 = 0;

if Y_clust2_psonic_lag1 >= 0.05 and Y_clust2_psonic_lag1 < 0.1 then incr5per_clust2_psonic = 1; else incr5per_clust2_psonic = 0;
if Y_clust4_psonic_lag1 >= 0.05 and Y_clust4_psonic_lag1 < 0.1 then incr5per_clust4_psonic = 1; else incr5per_clust4_psonic = 0;

if Y_clust2_psonic_lag1 <= -0.05 and Y_clust2_psonic_lag1 > -0.1 then decr5per_clust2_psonic = 1; else decr5per_clust2_psonic = 0;
if Y_clust4_psonic_lag1 <= -0.05 and Y_clust4_psonic_lag1 > -0.1 then decr5per_clust4_psonic = 1; else decr5per_clust4_psonic = 0;

if Y_clust2_psonic_lag1 >= 0.1 and Y_clust2_psonic_lag1 < 0.2 then incr10per_clust2_psonic = 1; else incr10per_clust2_psonic = 0;
if Y_clust4_psonic_lag1 >= 0.1 and Y_clust4_psonic_lag1 < 0.2 then incr10per_clust4_psonic = 1; else incr10per_clust4_psonic = 0;

if Y_clust2_psonic_lag1 <= -0.1 and Y_clust2_psonic_lag1 > -0.2 then decr10per_clust2_psonic = 1; else decr10per_clust2_psonic = 0;
if Y_clust4_psonic_lag1 <= -0.1 and Y_clust4_psonic_lag1 > -0.2 then decr10per_clust4_psonic = 1; else decr10per_clust4_psonic = 0;

if Y_clust2_psonic_lag1 >= 0.2 then incr20per_clust2_psonic = 1; else incr20per_clust2_psonic = 0;
if Y_clust4_psonic_lag1 >= 0.2 then incr20per_clust4_psonic = 1; else incr20per_clust4_psonic = 0;

if Y_clust2_psonic_lag1 <= -0.2 then decr20per_clust2_psonic = 1; else decr20per_clust2_psonic = 0;
if Y_clust4_psonic_lag1 <= -0.2 then decr20per_clust4_psonic = 1; else decr20per_clust4_psonic = 0;

/**Frigidaire**/

if Y_clust1_knmore_lag1 >= 0.05 and Y_clust1_knmore_lag1 < 0.1 then incr5per_clust1_knmore = 1; else incr5per_clust1_knmore = 0;
if Y_clust1_knmore_lag1 <= -0.05 and Y_clust1_knmore_lag1 > -0.1 then decr5per_clust1_knmore = 1; else decr5per_clust1_knmore = 0;

if Y_clust1_knmore_lag1 >= 0.1 and Y_clust1_knmore_lag1 < 0.2 then incr10per_clust1_knmore = 1; else incr10per_clust1_knmore = 0;
if Y_clust1_knmore_lag1 <= -0.1 and Y_clust1_knmore_lag1 > -0.2 then decr10per_clust1_knmore = 1; else decr10per_clust1_knmore = 0;

if Y_clust1_knmore_lag1 >= 0.2 then incr20per_clust1_knmore = 1; else incr20per_clust1_knmore = 0;
if Y_clust1_knmore_lag1 <= -0.2 then decr20per_clust1_knmore = 1; else decr20per_clust1_knmore = 0;

/**Kenmore**/
if Y_clust3_knmore_lag1 >= 0.05 and Y_clust3_knmore_lag1 < 0.1 then incr5per_clust3_knmore = 1; else incr5per_clust3_knmore = 0;
if Y_clust3_knmore_lag1 <= -0.05 and Y_clust3_knmore_lag1 > -0.1 then decr5per_clust3_knmore = 1; else decr5per_clust3_knmore = 0;

if Y_clust3_knmore_lag1 >= 0.1 and Y_clust3_knmore_lag1 < 0.2 then incr10per_clust3_knmore = 1; else incr10per_clust3_knmore = 0;
if Y_clust3_knmore_lag1 <= -0.1 and Y_clust3_knmore_lag1> -0.2 then decr10per_clust3_knmore = 1; else decr10per_clust3_knmore = 0;

if Y_clust3_knmore_lag1 >= 0.2 then incr20per_clust3_knmore = 1; else incr20per_clust3_knmore = 0;
if Y_clust3_knmore_lag1 <= -0.2 then decr20per_clust3_knmore = 1; else decr20per_clust3_knmore = 0;


/**LG**/
if Y_clust4_lg_lag1 >= 0.05 and Y_clust4_lg_lag1 < 0.1 then incr5per_clust4_lg = 1; else incr5per_clust4_lg = 0;
if Y_clust4_lg_lag1 <= -0.05 and Y_clust4_lg_lag1 > -0.1 then decr5per_clust4_lg = 1; else decr5per_clust4_lg = 0;

if Y_clust4_lg_lag1 >= 0.1 and Y_clust4_lg_lag1 < 0.2 then incr10per_clust4_lg = 1; else incr10per_clust4_lg = 0;
if Y_clust4_lg_lag1 <= -0.1 and Y_clust4_lg_lag1> -0.2 then decr10per_clust4_lg = 1; else decr10per_clust4_lg = 0;

if Y_clust4_lg_lag1 >= 0.2 then incr20per_clust4_lg = 1; else incr20per_clust4_lg = 0;
if Y_clust4_lg_lag1 <= -0.2 then decr20per_clust4_lg = 1; else decr20per_clust4_lg = 0;

run;

/**/
/*PROC EXPORT */
/*DATA=final_gam_modeldata*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\GAM + MVRF\Other categories\Microwaves\final_gam_modeldata_Rnd4July2025.csv"*/
/*REPLACE;*/
/*run;*/


PROC EXPORT 
DATA=final_gam_modeldata
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\GAM + MVRF\Other categories\Microwaves\final_gam_modeldata_RndaccNov2025.csv"
REPLACE;
run;


libname inn "D:\Amazon Price Dynamics\New Data\GAM + MVRF\Other categories\Microwaves";
data inn.final_gam_modeldata;
set final_gam_modeldata;
run;

proc freq data = final_gam_modeldata;
tables Y_amzn_lg_lag1;
run;
/**/
/*proc sql;*/
/*create table summ_num_changes as*/
/*select*/
/*sum(case when Y_amzn_lg_lag1 ne 0 then 1 else 0 end) as num_amzn_lgchanges,*/
/*sum(case when Y_amzn_psonic_lag1 ne 0 then 1 else 0 end) as num_amzn_psonicchanges,*/
/*sum(case when Y_clust2_psonic_lag1 ne 0 then 1 else 0 end) as num_3p2_psonicchanges,*/
/*sum(case when Y_clust4_psonic_lag1 ne 0 then 1 else 0 end) as num_3p4_psonicchanges,*/
/*sum(case when Y_clust1_frigid_lag1 ne 0 then 1 else 0 end) as num_3p1_frigidchanges,*/
/*sum(case when Y_clust3_knmore_lag1 ne 0 then 1 else 0 end) as num_3p3_knmorechanges,*/
/**/
/*sum(case when incr5per_amzn_lg ne 0 then 1 else 0 end) as num_incr5peramzn_lgchanges,*/
/*sum(case when decr5per_amzn_lg ne 0 then 1 else 0 end) as num_decr5peramzn_lgchanges,*/
/*sum(case when incr10per_amzn_lg ne 0 then 1 else 0 end) as num_incr10peramzn_lgchanges,*/
/*sum(case when decr10per_amzn_lg ne 0 then 1 else 0 end) as num_decr10peramzn_lgchanges,*/
/*sum(case when incr20per_amzn_lg ne 0 then 1 else 0 end) as num_incr20peramzn_lgchanges,*/
/*sum(case when decr20per_amzn_lg ne 0 then 1 else 0 end) as num_decr20peramzn_lgchanges,*/
/**/
/*sum(case when incr5per_amzn_psonic ne 0 then 1 else 0 end) as num_incr5peramzn_psonicchanges,*/
/*sum(case when decr5per_amzn_psonic ne 0 then 1 else 0 end) as num_decr5peramzn_psonicchanges,*/
/*sum(case when incr10per_amzn_psonic ne 0 then 1 else 0 end) as num_incr10peramzn_psonicchanges,*/
/*sum(case when decr10per_amzn_psonic ne 0 then 1 else 0 end) as num_decr10peramzn_psonicchanges,*/
/*sum(case when incr20per_amzn_psonic ne 0 then 1 else 0 end) as num_incr20peramzn_psonicchanges,*/
/*sum(case when decr20per_amzn_psonic ne 0 then 1 else 0 end) as num_decr20peramzn_psonicchanges,*/
/**/
/*sum(case when incr5per_3p_psonic ne 0 then 1 else 0 end) as num_incr5per3p_psonicchanges,*/
/*sum(case when decr5per_3p_psonic ne 0 then 1 else 0 end) as num_decr5per3p_psonicchanges,*/
/*sum(case when incr10per_3p_psonic ne 0 then 1 else 0 end) as num_incr10per3p_psonicchanges,*/
/*sum(case when decr10per_3p_psonic ne 0 then 1 else 0 end) as num_decr10per3p_psonicchanges,*/
/*sum(case when incr20per_3p_psonic ne 0 then 1 else 0 end) as num_incr20per3p_psonicchanges,*/
/*sum(case when decr20per_3p_psonic ne 0 then 1 else 0 end) as num_decr20per3p_psonicchanges,*/
/**/
/*sum(case when incr5per_3p_frigid ne 0 then 1 else 0 end) as num_incr5per3p_frigidchanges,*/
/*sum(case when decr5per_3p_frigid ne 0 then 1 else 0 end) as num_decr5per3p_frigidchanges,*/
/*sum(case when incr10per_3p_frigid ne 0 then 1 else 0 end) as num_incr10per3p_frigidchanges,*/
/*sum(case when decr10per_3p_frigid ne 0 then 1 else 0 end) as num_decr10per3p_frigidchanges,*/
/*sum(case when incr20per_3p_frigid ne 0 then 1 else 0 end) as num_incr20per3p_frigidchanges,*/
/*sum(case when decr20per_3p_frigid ne 0 then 1 else 0 end) as num_decr20per3p_frigidchanges,*/
/**/
/*sum(case when incr5per_3p_knmore ne 0 then 1 else 0 end) as num_incr5per3p_knmorechanges,*/
/*sum(case when decr5per_3p_knmore ne 0 then 1 else 0 end) as num_decr5per3p_knmorechanges,*/
/*sum(case when incr10per_3p_knmore ne 0 then 1 else 0 end) as num_incr10per3p_knmorechanges,*/
/*sum(case when decr10per_3p_knmore ne 0 then 1 else 0 end) as num_decr10per3p_knmorechanges,*/
/*sum(case when incr20per_3p_knmore ne 0 then 1 else 0 end) as num_incr20per3p_knmorechanges,*/
/*sum(case when decr20per_3p_knmore ne 0 then 1 else 0 end) as num_decr20per3p_knmorechanges*/
/**/
/*from */
/*final_gam_modeldata;*/
/*quit;*/
/*proc print data = summ_num_changes;*/
/*run;*/
/**/
/**/
/**/
/**/
/*proc contents data = final_gam_modeldata;*/
/*run;*/
